﻿''' <summary>
''' Contains information for running and return raw SQL commands.
''' </summary>
''' <author>AJ Whatling, Feng Jiang, Rick Simpson</author>
Public Class LibraryBookDataTier

    Private Shared MyConnection As System.Data.OleDb.OleDbConnection
    Private Shared Ready As Boolean = False

    ''' <summary>
    ''' Return information about the status of this data tier class.
    ''' </summary>
    ''' <returns>True if the data is ready to be accessed. False otherwise.</returns>
    ReadOnly Property IsReady() As Boolean
        Get
            Return Ready
        End Get
    End Property

    ''' <summary>
    ''' Initializes the internal connection.
    ''' </summary>
    Public Shared Sub Initialize()
        If (Not Ready) Then
            MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\mis431library.mdb;Persist Security Info=True")
            MyConnection.Open()
            Ready = True
        End If
    End Sub

    ''' <summary>
    ''' Close down internal DB connections and free up extra resources.
    ''' </summary>
    Public Shared Sub CloseDown()
        Ready = False
        If (Not MyConnection Is Nothing) Then
            MyConnection.Close()
        End If
    End Sub

    ''' <summary>
    ''' Execute a raw SQL command and return a DataReader.  Note that this command cannot 
    ''' be one that CHANGES the database.
    ''' </summary>
    ''' <param name="sql">Raw SQL string to execute.</param>
    ''' <returns>OleDbDataReader object.</returns>
    Public Shared Function ExecuteSelect(ByVal sql As String) As System.Data.OleDb.OleDbDataReader
        Initialize()
        Dim MyCommand As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand(sql, MyConnection)
        Return MyCommand.ExecuteReader()
    End Function

    ''' <summary>
    ''' Execute raw SQL that modifies the database.
    ''' </summary>
    ''' <param name="sql">Raw SQL string to execute</param>
    Public Shared Sub ExecuteModify(ByVal sql As String)
        Initialize()
        Dim MyCommand As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand(sql, MyConnection)
        MyCommand.ExecuteNonQuery()
    End Sub

    ''' <summary>
    ''' Return the connection used by this data tier, initializing it if it hasn't been.
    ''' </summary>
    ''' <returns>OleDbConnection object.</returns>
    Public Shared Function GetConnectionObject() As System.Data.OleDb.OleDbConnection
        Initialize()
        Return MyConnection
    End Function

    ''' <summary>
    ''' Get the number of books checked out by the given username.
    ''' </summary>
    ''' <param name="username">User name to check against.</param>
    ''' <returns>The number of books checked out by the given user.</returns>
    Public Shared Function UserGetNumBooksCheckedOut(ByVal username As String) As Integer
        Dim UserQuery As String = "SELECT COUNT(*) As CheckOutCount FROM ServedBooks WHERE transactionType='c' AND userID='" & username & "'"

        Dim ResultSet As System.Data.OleDb.OleDbDataReader = ExecuteSelect(UserQuery)

        If (ResultSet.HasRows) Then
            ResultSet.Read()
            Dim RetInteger As Integer
            Try
                RetInteger = Integer.Parse(ResultSet.Item(0))
            Catch ex As Exception
                ResultSet.Close()
                CloseDown()
                Return 0
            End Try
            ResultSet.Close()
            CloseDown()
            Return RetInteger
        End If
        ResultSet.Close()
        CloseDown()
        Return 0
    End Function

    ''' <summary>
    ''' Get the number of books reserved by the given username.
    ''' </summary>
    ''' <param name="username">User name to check against.</param>
    ''' <returns>The number of books reserved by the given user.</returns>
    Public Shared Function UserGetNumBooksReserved(ByVal username As String) As Integer
        Dim UserQuery As String = "SELECT COUNT(*) As ReservedCount FROM ServedBooks WHERE transactionType='r' AND userID='" & username & "'"

        Dim ResultSet As System.Data.OleDb.OleDbDataReader = ExecuteSelect(UserQuery)

        If (ResultSet.HasRows) Then
            ResultSet.Read()
            Dim RetInteger As Integer
            Try
                RetInteger = Integer.Parse(ResultSet.Item(0))
            Catch ex As Exception
                ResultSet.Close()
                CloseDown()
                Return 0
            End Try
            ResultSet.Close()
            CloseDown()
            Return RetInteger
        End If
        ResultSet.Close()
        CloseDown()
        Return 0
    End Function

    ''' <summary>
    ''' Determine if a book is unavailable.
    ''' </summary>
    ''' <param name="bid">Book ID.</param>
    ''' <returns>True if the book is unavailable. False if it is available.</returns>
    Public Shared Function BookIsUnavailable(ByVal bid As Integer) As Boolean
        Dim BookQuery As String = "SELECT checkOutStatus, reserveStatus FROM Books WHERE book_ID=" & bid.ToString

        Dim ResultSet As System.Data.OleDb.OleDbDataReader = ExecuteSelect(BookQuery)

        If ResultSet.HasRows Then
            ResultSet.Read()
            Dim BookChkOutStatus As String = ResultSet(0)
            Dim BookRsvStatus As String = ResultSet(1)

            Return BookChkOutStatus.Equals("1") OrElse BookRsvStatus.Equals("1")
        End If

        Return True
    End Function

    ''' <summary>
    ''' Return a book by removing its entry from the ServedBooks table
    ''' and updating its record in the books table.
    ''' </summary>
    ''' <param name="bid">Book ID of the book to return.</param>
    ''' <param name="uname">Username of the user who is currently holding the book.</param>
    Public Shared Sub ReturnBook(ByVal bid As String, ByVal uname As String)
        Dim PayAndReturnQuery As String = "DELETE FROM ServedBooks WHERE userID='" & uname & "' AND bookID=" & bid
        Dim UpdateBookEntryQuery As String = "UPDATE Books SET checkOutStatus=0, reserveFrequency=0 WHERE book_ID=" & bid
        LibraryBookDataTier.CloseDown()
        LibraryBookDataTier.ExecuteModify(PayAndReturnQuery)
        LibraryBookDataTier.CloseDown()
        LibraryBookDataTier.ExecuteModify(UpdateBookEntryQuery)
        LibraryBookDataTier.CloseDown()
    End Sub
    ''' <summary>
    ''' Renews a book.
    ''' </summary>
    ''' <param name="bid">Book ID of the book to renew.</param>
    ''' <param name="uname">Username of the user who is currently holding the book.</param>
    Public Shared Sub RenewBook(ByVal bid As String, ByVal uname As String)
        Dim UpdateSbEntry As String = "UPDATE ServedBooks SET userID='" & uname & "', borrowedDate='" & Date.Today.ToString & "', returnDate='" & DateAdd(DateInterval.Month, 6, Date.Today).ToString & "' WHERE bookID=" & bid
        LibraryBookDataTier.CloseDown()
        LibraryBookDataTier.ExecuteModify(UpdateSbEntry)
        LibraryBookDataTier.CloseDown()
    End Sub
End Class
